﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;

using Community.CsharpSqlite.SQLiteClient;

namespace VSTalk.Engine.Core.Disco.Caps
{
    public class CapsDiscoveryDA : ICapsDicoveryDA
    {
        private SqliteConnection _connection;

        public void Init(SqliteConnection connection)
        {
            _connection = connection;

        }
        public bool HasCaps(string node, string hash, string version)
        {
            _connection.Open();
            var command = _connection.CreateCommand();
            command.CommandText = @"
select exists(select 1 from caps_info where node=@p1 and hash=@p2 and version=@p3 limit 1)
";
            command.CommandType = CommandType.Text;
            command.Parameters.Add(new SqliteParameter("@p1", node));
            command.Parameters.Add(new SqliteParameter("@p2", hash));
            command.Parameters.Add(new SqliteParameter("@p3", version));
            var reader = command.ExecuteReader();
            reader.Read();
            var result = reader.GetBoolean(0);
            _connection.Close();
            return result;
        }

        public void RecordFeatures(string node, string hash, string version, IList<string> features)
        {
            _connection.Open();
            var command = _connection.CreateCommand();
            command.CommandText = Array(features) + @"
insert into feature (uri)
select * FROM str_arr 
where NOT exists( SELECT 1 FROM feature f where f.uri = str_arr.item);

INSERT INTO caps_info(node, version, hash) 
select @p1, @p2, @p3
where not exists(SELECT 1 FROM caps_info WHERE node = @p1 and version = @p2 and hash= @p3);

insert INTO caps_to_feature(caps_info_id, feature_id) 
SELECT c.Id, f.Id FROM feature f JOIN caps_info c 
where f.uri in (select * FROM str_arr) and node = @p1 and version = @p2 and hash= @p3
and NOT exists(SELECT 1 FROM caps_to_feature cf WHERE cf.caps_info_id = c.id and cf.feature_id = f.id);
";
            command.Parameters.Add(new SqliteParameter("@p1", node));
            command.Parameters.Add(new SqliteParameter("@p2", version));
            command.Parameters.Add(new SqliteParameter("@p3", hash));
            
            for (int i = 0; i < features.Count; i++)
            {
                command.Parameters.Add(new SqliteParameter("@i" + i, features[i]));
            }
            command.ExecuteNonQuery();
            _connection.Close();
            
        }

        private string Array(IList<string> param)
        {
            if (param.Count == 0)
            {
                return "CREATE TEMP TABLE str_arr;";
            }
            return string.Format("CREATE TEMP TABLE str_arr AS select * from ({0});", string.Join("UNION ALL", 
                param.Select((s, i) =>
                {
                    if (i != 0)
                    {
                        return string.Format(" SELECT @i{0} ", i);
                    }
                    return string.Format(" SELECT @i{0} as item ", i);
                })));
        }

        public bool HasFeature(string node, string hash, string version, string uri)
        {
            _connection.Open();
            var command = _connection.CreateCommand();
            command.CommandText = @"
select exists(select 1 
from caps_info i
join caps_to_feature cf on i.id = cf.caps_info_id
join feature f on f.id = cf.feature_id
where i.node=@p1 and i.hash=@p2 and i.version=@p3 and f.uri=@p4 limit 1)
";
            command.CommandType = CommandType.Text;
            command.Parameters.Add(new SqliteParameter("@p1", node));
            command.Parameters.Add(new SqliteParameter("@p2", hash));
            command.Parameters.Add(new SqliteParameter("@p3", version));
            command.Parameters.Add(new SqliteParameter("@p4", uri));
            var reader = command.ExecuteReader();
            reader.Read();
            var result = reader.GetBoolean(0);
            _connection.Close();
            return result;
        }
    }
}